<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace App;
class SQLDatabase extends Database {

    /**
     * create_table($table_def) - tbd
     * @param string  table_name         [IN] - the name of the table
     * @param string  table_label        [IN] - (optional) the label (text string) used to describe this table in the user interface (default is the table_name, converted to a label)
     * @param string  columns            [IN] - either a (csv) list of column_names or an array of column_names
     * @param string  phys_columns       [IN] - (optional) either a (csv) list of column_names or an array of column_names
     *                                          (if phys_columns exists, it overrides the columns value, which is instead interpreted to be a list of logical columns)
     * @param object  column             [IN] - (optional) an associated array of associative arrays with metadata about the columns
     *                                          e.g. array('person_id'=>array('column_name'=>'person_id','column_label'=>'Person ID','column_type'=>'integer','max_length'=>'10','not_null_ind'=>'Y',
                                                           'primary_key_ind'=>'Y','auto_increment_ind'=>'Y','default_value'=>null,'display_format'=>null,'display_justify'=>null))
     * @param array   primary_key        [IN] - (optional) an array of column_names which make up the primary key (if single column 'id' or ending in '_id', then it is an autoincrement column)
     * @param string  auto_increment_column [IN] - (optional) a column_name which is an autoincrement column
     * @param array   unique_indexes     [IN] - (optional) an array of arrays of column_names which make up alternate keys (uniqueness constraints with indexes)
     * @param array   indexes            [IN] - (optional) an array of arrays of column_names which make up non-unique indexes
     */
    public function create_table (&$table_def) {
        global $trace;
        if ($trace) trace_entry();
        $ddls = array();
        $this->make_create_table_ddl($table_def, $ddls);
        foreach ($ddls as $ddl) {
            $this->execute($ddl);
        }
        $this->refresh_table_metadata($this->schema_name, $table_def['table_name']);
        if ($trace) trace_exit();
    }

    /**
     * alter_table($table_def) - tbd
     * @param string  table_name         [IN] - the name of the table
     * @param string  table_label        [IN] - (optional) the label (text string) used to describe this table in the user interface (default is the table_name, converted to a label)
     * @param string  columns            [IN] - either a (csv) list of column_names or an array of column_names
     * @param string  phys_columns       [IN] - (optional) either a (csv) list of column_names or an array of column_names
     *                                          (if phys_columns exists, it overrides the columns value, which is instead interpreted to be a list of logical columns)
     * @param object  column             [IN] - (optional) an associated array of associative arrays with metadata about the columns
     *                                          e.g. array('person_id'=>array('column_name'=>'person_id','column_label'=>'Person ID','column_type'=>'integer','max_length'=>'10','not_null_ind'=>'Y',
                                                           'primary_key_ind'=>'Y','auto_increment_ind'=>'Y','default_value'=>null,'display_format'=>null,'display_justify'=>null))
     * @param array   primary_key        [IN] - (optional) an array of column_names which make up the primary key (if single column 'id' or ending in '_id', then it is an autoincrement column)
     * @param string  auto_increment_column [IN] - (optional) a column_name which is an autoincrement column
     * @param array   unique_indexes     [IN] - (optional) an array of arrays of column_names which make up alternate keys (uniqueness constraints with indexes)
     * @param array   indexes            [IN] - (optional) an array of arrays of column_names which make up non-unique indexes
     */
    public function alter_table (&$table_def, $table_def_from_db = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($table_def['table_name'])) { throw new \Exception("ERROR: you must supply a table_name to alter that table"); }
        $table_name = $table_def['table_name'];
        if (!$table_def_from_db) $table_def_from_db = $this->get_table_def_from_db($table_name, $this->schema_name);
        $ddls = array();
        $this->make_alter_table_ddl($table_def, $table_def_from_db, $ddls);
        foreach ($ddls as $ddl) {
            $this->execute($ddl);
        }
        $this->refresh_table_metadata($this->schema_name, $table_def['table_name']);
        if ($trace) trace_exit();
    }

    /**
     * drop_table($table_name) - tbd
     * @param string  table_name         [IN] - the name of the table
     */
    public function drop_table ($table_name) {
        global $trace;
        if ($trace) trace_entry();
        $ddls = array();
        $this->make_drop_table_ddl($table_name, $ddls);
        foreach ($ddls as $ddl) {
            $this->execute($ddl);
        }

        $metadb     = $this->metadb();
        $schema_def = $this->get_schema_def($this->schema_name);
        $schema_id  = $schema_def['schema_id'];
        $raw_options = array('raw'=>1);
        $table_id   = $metadb->get('db_table', array('schema_id'=>$schema_id, 'table_name'=>$table_name), 'table_id', $raw_options);
        #$metadb->delete('db_column_set_columns', array('column_set_id'=>$column_set_id));
        #$metadb->delete('db_column_set', array('table_id'=>$table_id));
        $metadb->delete('db_column', array('table_id'=>$table_id), $raw_options);
        #$metadb->delete('db_index_column', array('index_id'=>$index_id));
        #$metadb->delete('db_index', array('table_id'=>$table_id));
        $metadb->delete('db_table', array('table_id'=>$table_id), $raw_options);

        $db_name = $this->db_name;
        $metadb->execute("delete from {meta_schema_}app_store where store_type = 'grid' and store_key =    '$db_name.$table_name'");
        $metadb->execute("delete from {meta_schema_}app_store where store_type = 'grid' and store_key like '$db_name.$table_name.%'");

        if ($trace) trace_exit();
    }

    public function rename_table ($table_name, $new_table_name, $new_table_label = null) {
        global $trace;
        if ($trace) trace_entry();

        ###############################################################
        # update the database tables
        ###############################################################
        $ddls = array();
        $this->make_rename_table_ddl($table_name, $new_table_name, $ddls);
        foreach ($ddls as $ddl) {
            $this->execute($ddl);
        }

        ###############################################################
        # update the stored metadata about those tables
        ###############################################################
        # $this->_rename_table_metadata($table_name, $new_table_name, $new_table_label);
        $metadb     = $this->metadb();
        $db_name    = $this->db_name;
        $schema_def = $this->get_schema_def($this->schema_name);
        $schema_id  = $schema_def['schema_id'];
        if (!isset($new_table_label)) {
            $new_table_label = $this->make_label($new_table_name);
        }
        $new_table_alias = $this->make_table_alias($new_table_name);
        $table_id   = $metadb->get('db_table', array('schema_id'=>$schema_id, 'table_name'=>$table_name), 'table_id');
        $metadb->execute("update {meta_schema_}db_table set table_name = ?, table_label = ?, table_alias = ? where table_id = ?",
            array($new_table_name, $new_table_label, $new_table_alias, $table_id));

        $metadb->execute("update {meta_schema_}app_store set store_key = ?, content_str = ? where store_type = 'grid' and store_key = ?",
            array("$db_name.$new_table_name", $new_table_label, "$db_name.$table_name"));
        $views = $metadb->get_hashes("select store_key, content_str from {meta_schema_}app_store where store_type = 'grid' and store_key like '$db_name.$table_name.%'");
        foreach ($views as $view) {
            $store_key = preg_replace("/^$db_name\\.$table_name\\.(.+)/", "$db_name.$new_table_name.\$1", $view['store_key']);
            $metadb->execute("update {meta_schema_}app_store set store_key = ? where store_type = 'grid' and store_key = ?",
                array($store_key, $view['store_key']));
        }

        ###############################################################
        # update the in-memory cached metadata about those tables
        ###############################################################
        $this->_rename_table_cache($table_name, $new_table_name, $new_table_label);

        $this->rename_table_metadata($this->schema_name, $new_table_name, $table_name);
        $this->refresh_table_metadata($this->schema_name, $new_table_name);

        if ($trace) trace_exit();
    }

    public function export_table ($table_name, $filename = null, $options = null) {
        global $trace;
        if ($trace) trace_entry();
        $sqls = array();
        $this->make_export_table_sql($table_name, $filename, $sqls);
        foreach ($sqls as $sql) {
            $this->execute($sql);
        }
        if ($trace) trace_exit();
    }

    public function import_table ($table_name, $filename = null, $options = null) {
        global $trace;
        if ($trace) trace_entry();
        $sqls = array();

        $this->make_import_table_sql($table_name, null, $options, $filename, $sqls);
        foreach ($sqls as $sql) {
            $this->execute($sql);
        }
        if ($trace) trace_exit();
    }

    # Execute a sequence of SQL statements (terminated by semicolons) in a file.
    # "--" comments are allowed on their own lines and at the end of lines
    public function run_file ($file) {
        global $trace;
        if ($trace) trace_entry();
        $fh = fopen($file, 'r');
        if (!$fh) throw new \Exception("ERROR: cannot open file [$file] for reading");
        $sql = '';
        $matches = array();
        while (1) {
            $line = fgets($fh);
            if ($line === false) break;

            # search for and remove SQL comments
            $pos = strpos($line, '--');
            if ($pos !== false) {
                if ($pos == 0) $line = '';
                else           $line = substr($line, 0, $pos);
            }
            $line = rtrim($line);
            if ($line === '') continue;

            # search for semicolons, remove them, and execute the statement
            $len = strlen($line);
            if ($line[$len-1] === ';') {
                $line = substr($line, 0, $len-1);
                $line = rtrim($line);
                if ($line !== '') $sql .= $line . "\n";
                $this->execute($sql);
                $sql = '';
            }
            else {
                $sql .= $line . "\n";   # no execute yet. just accumulate the SQL statement.
            }
        }
        fclose($fh);
        if ($trace) trace_exit();
    }

    protected function _get_select_sql ($table, $params = null, $columns = null, &$options = null, &$bind_values = null) {
        global $trace;
        if ($trace) trace_entry();

        $bind = isset($bind_values);
        if (preg_match('/[^A-Za-z0-9_\\.]/',$table)) {  # if anything besides normal table characters, it must be a full SQL statement
            $sql = $table;
            if (preg_match('/\\{[a-zA-Z0-9.,_-]+\\}/',$sql)) $sql = $this->substitute_sql_vars($sql);
            if ($options && isset($options['limit'])) {
                $this->add_limit_clause($sql, $options);
            }
            if ($bind && isset($params)) {
                if (is_scalar($params)) {
                    $bind_values[] = $params;
                }
                else {
                    foreach ($params as $bind_value) {
                        $bind_values[] = $bind_value;
                    }
                }
            }
        }
        else {
            $sql = $this->make_select_sql($table, $params, $columns, $options, $bind_values);
        }
        if (isset($options) && isset($options['sql'])) $options['sql'] = $sql;
        if ($trace) trace_exit($sql);
        return($sql);
    }

    public function make_select_sql ($table, $params, $columns = null, &$options = null, &$bind_values = null) {
        global $trace;
        if ($trace) trace_entry();

        $bind = isset($bind_values);

        if (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $table, $matches)) {
            $schema    = $matches[1];
            $fulltable = $table;
            $table     = $matches[2];
            $select_table = $fulltable;
        }
        else {
            if (!isset($schema)) $schema = $this->schema_name;
            $fulltable = "$schema.$table";
            $select_table = $fulltable;
            #$select_table = $table;
        }

        $raw       = (isset($options) && isset($options['raw'])       && $options['raw'])       ? 1 : 0;
        $aggregate = (isset($options) && isset($options['aggregate']) && $options['aggregate']) ? 1 : 0;
        #debug_print("make_select_sql(table=[$table], params, columns=[".implode(",",$columns)."], aggregate=$aggregate");
        if (! isset($params)) $params = array();

        if ($raw) {
            $where_clause = '';
            $this->_make_where_clause($table, $params, $where_clause, $bind_values);
            $sql = "select\n" .
                   implode(",\n   ", $columns) .
                   "\nfrom $select_table" .
                   $where_clause .
                   $this->_make_order_by_clause($table, $options) .
                   $this->_make_limit_clause($table, $options);
        }
        else {
            $table_def   = &$this->get_table_def($table, $schema, $options);   # get a reference
            $table_alias = $table_def['table_alias'];

            $query_column_defs = null;
            if ($options && isset($options['column_defs'])) {
                $query_column_defs = $options['column_defs'];
            }

            if (! isset($columns)) {
                if (isset($table_def['default_columns'])) {
                    $columns = $table_def['default_columns'];
                }
                elseif (isset($table_def['phys_columns'])) {
                    $columns = $table_def['phys_columns'];
                }
                else {
                    throw new \Exception('ERROR: make_select_sql(): columns parameter missing');
                }
            }

            $select_column_exprs   = array();
            $select_dbexprs        = array();
            $group_column_exprs    = array();
            $select_columns_missed = array();
            $rel_used              = array();

            for ($c = 0; $c < sizeof($columns); $c++) {
                $column = $columns[$c];
                #echo "First Pass: column=[$column]\n";

                $modifier = null;
                $parent_column = null;
                if (preg_match('/^(.*)__([0-9]*[a-z]+)$/', $column, $matches)) {
                    $parent_column = $matches[1];
                    $modifier      = $matches[2];
                }

                if (isset($table_def['column'][$column])) {                   # it is a known column
                    if (isset($query_column_defs) && isset($query_column_defs[$column])) $column_def =& $query_column_defs[$column];
                    else                                                                 $column_def =& $table_def['column'][$column];
                    #if (isset($modifier)) $column_def['column_modifier'] = $modifier;
                    #echo "column=[$column] found column_def\n";
                    $this->_make_select_column($table_def, $c, $column, $column_def, $select_column_exprs, $select_dbexprs, $group_column_exprs, $aggregate);
                    if (isset($column_def['relationship_alias'])) {
                        $relationship_alias = $column_def['relationship_alias'];
                        $schema_name        = isset($column_def['schema_name']) ? $column_def['schema_name'] : 'Y';
                        $rel_used[$relationship_alias] = $schema_name;
                    }
                }
                elseif (preg_match('/^(.*) +as +([A-Za-z_][A-Za-z0-9_]*) *$/', $column, $matches)) {   # it looks like an expression with an alias
                    $select_column_exprs[$c] = $column;
                    $select_dbexprs[$c]      = $matches[1];
                    #echo "column=[$column] column was a dbexpr with 'as column_name' portion\n";
                    $column                  = $matches[2];
                }
                elseif (preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $column)) {  # it looks like a regular column... (we'd better check the columns of all related tables)
                    #$select_column_exprs[$c] = "NULL as $column";            # don't jump to this conclusion yet. we need to check the "extra column defs" (from relationships)
                    #$select_dbexprs[$c]      = 'NULL';                       # don't jump to this conclusion yet. we need to check the "extra column defs" (from relationships)
                    $select_column_exprs[$c] = null;
                    $select_dbexprs[$c]      = null;
                    $select_columns_missed[] = $column;
                    #echo "column=[$column] column was a column without any column_def\n";
                }
                else {                                                        # it must be an expression
                    $select_column_exprs[$c] = "$column as c$c";
                    $select_dbexprs[$c]      = $column;
                    #echo "column=[$column] column was a dbexpr\n";
                    $column                  = "c$c";
                }
            }

            if (count($select_columns_missed) > 0) {
                $this->_load_extra_column_defs($schema, $table, null, $select_columns_missed);
                $table_def =& $this->table_defs[$schema][$table];   # get a fresh look

                for ($c = 0; $c < sizeof($columns); $c++) {
                    $column = $columns[$c];

                    if ($select_column_exprs[$c] === null) {
                        if (isset($table_def['column'][$column])) {
                            $column_def =& $table_def['column'][$column];
                            $this->_make_select_column($table_def, $c, $column, $column_def, $select_column_exprs, $select_dbexprs, $group_column_exprs, $aggregate);
                            if (isset($table_def['column'][$column]['relationship_alias'])) {
                                $relationship_alias = $table_def['column'][$column]['relationship_alias'];
                                $schema_name        = isset($table_def['column'][$column]['schema_name']) ? $table_def['column'][$column]['schema_name'] : 'Y';
                                $rel_used[$relationship_alias] = $schema_name;
                            }
                        }
                        else {
                            $select_column_exprs[$c] = "null as $column";
                            $select_dbexprs[$c]      = 'null';
                        }
                    }
                }
            }

            $where_clause = '';
            $having_clause = '';
            $this->_make_where_clause($table, $params, $where_clause, $bind_values, $having_clause, $aggregate, $table_def, $rel_used);
            $order_by_clause = $this->_make_order_by_clause($table, $options, $table_def, $rel_used);

            ##########################################################################################
            # join clause for other tables
            ##########################################################################################
            $joins = array();
            if (count($rel_used) > 0) {
                $relationships =& $table_def['relationship'];
                foreach ($relationships as $relationship) {
                    $relationship_alias = $relationship['relationship_alias'];
                    if (isset($rel_used[$relationship_alias]) && isset($relationship['dependencies'])) {
                        $dependencies = $relationship['dependencies'];
                        if ($dependencies && $dependencies != $table_alias) {
                            $dependencies = explode(',', $dependencies);
                            foreach ($dependencies as $dependency) {
                                if ($dependency != $table_alias && isset($relationships[$dependency]) && !isset($rel_used[$dependency])) {
                                    $rel_used[$dependency] = "Y";
                                }
                            }
                        }
                    }
                }
                foreach ($relationships as $relationship) {
                    $relationship_alias = $relationship['relationship_alias'];
                    if (isset($rel_used[$relationship_alias])) {
                        $joins[] = ($relationship['to_zero_ind'] === 'Y' ? 'left join ' : 'inner join ') .
                                    (($rel_used[$relationship_alias] != 1 && $rel_used[$relationship_alias] != 'Y') ? $rel_used[$relationship_alias].'.' : '') .
                                    $relationship['to_table'] . ' ' .
                                    $relationship['relationship_alias'] . ' on ' .
                                    $relationship['join_on_clause'];
                    }
                }
            }
            $join_clause = (count($joins) > 0) ? ("\n     " . implode("\n     ", $joins)) : '';

            if ($aggregate) {
                $group_by_clause = (count($group_column_exprs) > 0) ? ("\ngroup by\n   " . implode(",\n   ", $group_column_exprs)) : '';
                $having_clause = '';

                $sql = "select\n   " .
                        implode(",\n   ", $select_column_exprs) .
                        "\nfrom $select_table $table_alias" .
                        $join_clause .
                        $where_clause .
                        $group_by_clause .
                        $having_clause .
                        $order_by_clause .
                        $this->_make_limit_clause($table, $options);
            }
            else {
                $sql = "select\n   " .
                        implode(",\n   ", $select_column_exprs) .
                        "\nfrom $select_table $table_alias" .
                        $join_clause .
                        $where_clause .
                        $order_by_clause .
                        $this->_make_limit_clause($table, $options);
            }
        }

        if ($trace) trace_exit($sql);
        return($sql);
    }

    private function _make_select_column (&$table_def, $c, $column, &$column_def, &$select_column_exprs, &$select_dbexprs, &$group_column_exprs, $aggregate = 0) {
        global $trace;
        if ($trace) trace_entry();

        $relationship_alias = (isset($column_def['relationship_alias'])) ? $column_def['relationship_alias'] : null;
        if ($relationship_alias) {
            if (!isset($rel_used[$relationship_alias])) {
                $rel_used[$relationship_alias] = (isset($column_def['schema_name'])) ? $column_def['schema_name'] : 'Y';
            }
        }
        else {
            $relationship_alias = $table_def['table_alias'];
        }

        $is_key = (isset($column_def['agg_key_ind']) && $column_def['agg_key_ind'] === 'Y') ? 1 : 0;

        $column_modifier = isset($column_def['column_modifier']) ? $column_def['column_modifier'] : null;
        $column_type     = isset($column_def['column_type'])     ? $column_def['column_type']     : null;
        if ($column_modifier) {
            if (isset($column_def['parent'])) {
                $phys_column = $column_def['parent'];
                #echo "_make_select_column(): column=[$column] column_modifier=[$column_modifier] phys_column=[$phys_column]\n";
                $dbexpr = $this->column_modified($column, $column_modifier, "$relationship_alias.$phys_column", $table_def['column'][$phys_column], $table_def, $aggregate);
            }
            else {
                $dbexpr = $this->column_modified($column, $column_modifier, "$relationship_alias.$column", $column_def, $table_def, $aggregate);
            }
            #debug_print("dbexpr=[$dbexpr] : column_modifier=[$column_modifier]");
        }
        elseif ($aggregate && $is_key && $column_type === 'datetime') {
            $dbexpr = $this->column_modified($column, 'hour', "$relationship_alias.$column", $column_def, $table_def, $aggregate);
            #debug_print("dbexpr=[$dbexpr] : aggregate=[$aggregate] column_type=[$column_type]");
        }
        elseif ($aggregate && isset($column_def['agg_dbexpr'])) {
            $dbexpr = $column_def['agg_dbexpr'];
            #debug_print("dbexpr=[$dbexpr] : aggregate=[$aggregate] agg_dbexpr");
        }
        elseif ($aggregate && !$is_key) {
            $dbexpr = (isset($column_def['dbexpr'])) ? $column_def['dbexpr'] : "$relationship_alias.$column";
            $dbexpr = "sum($dbexpr)";
            #debug_print("dbexpr=[$dbexpr] : aggregate=[$aggregate] not key");
        }
        elseif (isset($column_def['dbexpr'])) {
            $dbexpr = $column_def['dbexpr'];
            #debug_print("dbexpr=[$dbexpr] : dbexpr literal");
        }
        else {
            $dbexpr = "$relationship_alias.$column";
            #debug_print("dbexpr=[$dbexpr] : from column name");
        }
        #debug_print("dbexpr=[$dbexpr] : otherwise");
        #debug_print("dbexpr=[$dbexpr] : aggregate=[$aggregate] is_key=[$is_key]");

        if ($column_def['column_type'] === 'date') {
            $dbexpr = $this->date_stdformat($dbexpr);
            #debug_print("dbexpr=[$dbexpr] : column_type=[date]");
        }
        elseif ($column_def['column_type'] === 'datetime') {
            $dbexpr = $this->datetime_stdformat($dbexpr);
            #debug_print("dbexpr=[$dbexpr] : column_type=[datetime]");
        }

        if ($aggregate && $is_key) $group_column_exprs[] = $dbexpr;
        $select_column_exprs[$c] = "$dbexpr as $column";
        $select_dbexprs[$c]      = $dbexpr;
        if ($trace) trace_exit();
    }

    protected function _make_where_clause ($table, $params, &$where_clause, &$bind_values = null, &$having_clause = null, $aggregate = 0, $table_def = null, &$rel_used = null) {
        global $trace;
        if ($trace) trace_entry();

        $bind          = isset($bind_values);
        $where_clause  = '';
        $having_clause = '';
        $matches       = array();
        $where_exprs   = array();

        if (is_scalar($params)) {
            if (!isset($table_def)) $table_def = $this->get_table_def($table);
            $primary_key_column = $this->get_primary_key_column($table, $table_def);
            $params = array("$primary_key_column-eq" => $params);
        }

        # This is MAGIC. If a column exists for owner_group_id, only users authenticated into a group_id can access those rows.
        # A user in the "system" group (2) is immune from this restriction.
        if (isset($table_def) && isset($table_def['column']['owner_group_id'])) {
            $current_group_id = $this->context->getAuthenticatedGroupId();
            if ($current_group_id != APP_SYSTEM_GROUP_ID) $params['owner_group_id-eq'] = $current_group_id;
        }

        $column_defs  = null;
        if (isset($table_def) && isset($table_def['column'])) $column_defs =& $table_def['column'];
        #echo "XXX: column_defs=[$column_defs]\n";

        foreach ($params as $param => $value) {
            $bindable_value = null;
            $column         = null;
            $rhs_op         = null;
            $sql_value      = null;
            $op_implied     = null;

            $col_expr_begin = '';
            $col_expr_end   = '';

            $resolved = 0;
            if (preg_match('/^([a-zA-Z0-9_]+)-([a-z]+)$/', $param, $matches)) {
                $column     = $matches[1];
                $op         = $matches[2];
                $sql_value  = $value;
                $op_implied = false;
                #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                $resolved = 1;
            }
            elseif (!isset($value)) {
                $column     = $param;
                $sql_value  = $value;
                $op         = 'eq';
                $op_implied = true;
                #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                $resolved = 1;
            }
            elseif (preg_match('/^(=~|~|=|!=|<>|>=|>|<=|<|!)(.*)$/', $value, $matches)) {
                $column     = $param;
                $rhs_op     = $matches[1];
                $sql_value  = $matches[2];
                $op_implied = false;
                if     ($rhs_op === '=')  { $op = 'eq'; }
                elseif ($rhs_op === '!')  { $op = 'ne'; $op_implied = true; }
                elseif ($rhs_op === '!=') { $op = 'ne'; }
                elseif ($rhs_op === '<>') { $op = 'ne'; }
                elseif ($rhs_op === '>=') { $op = 'ge'; }
                elseif ($rhs_op === '>')  { $op = 'gt'; }
                elseif ($rhs_op === '<=') { $op = 'le'; }
                elseif ($rhs_op === '<')  { $op = 'lt'; }
                elseif ($rhs_op === '~')  { $op = 'contains'; }
                elseif ($rhs_op === '=~') { $op = 'matches'; }
                else                     { continue; }   // skip this param altogether
                #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                $resolved = 1;
            }
            else {
                $column     = $param;
            }

            ###########################################################################################
            # Now that we know the column this parameter represents, we find out other info
            ###########################################################################################
            $column_def_found = 0;
            $type             = null;
            if (isset($column_defs) && isset($column_defs[$column])) {
                $column_def =& $column_defs[$column];
                $column_def_found = 1;
                if (isset($column_def['column_type'])) $type = $column_def['column_type'];
            }
            #echo "XXX: column_def=[$column_def] column=[$column] type=[$type] [". $this->assoc_as_string($column_def). "]\n";
            # TODO: these are shortcuts that should be put in a config file
            if (!isset($type)) {
                if     (preg_match('/_(dt|date)$/', $column)) $type = 'date';
                elseif (preg_match('/_dttm$/',      $column)) $type = 'datetime';
                elseif (preg_match('/_id$/',        $column)) $type = 'integer';
            }
            $is_date_type = (isset($type) && ($type === 'date' || $type === 'datetime'));

            if (!$resolved) {
                # DATE and DATETIME
                if ($is_date_type) {
                    if (preg_match('/^([0-9][0-9][0-9][0-9])-?([0-9][0-9])-?([0-9][0-9])[-:]([0-9][0-9][0-9][0-9])-?([0-9][0-9])-?([0-9][0-9])$/', $value, $matches)) {
                        $op_implied = true;
                        $op         = 'between';
                        $sql_op     = 'between';
                        $sql_value  = $this->date_literal($matches[1],$matches[2],$matches[3]) . ' and ' . $this->date_literal($matches[4],$matches[5],$matches[6]);
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                        $resolved = 1;
                    }
                    elseif (preg_match('/^([0-9][0-9][0-9][0-9])([0-9][0-9])([0-9][0-9])([0-9][0-9])([0-9][0-9])([0-9]?[0-9]?)[-:]([0-9][0-9][0-9][0-9])([0-9][0-9])([0-9][0-9])([0-9][0-9])([0-9][0-9])([0-9]?[0-9]?)$/', $value, $matches)) {
                        $op_implied = true;
                        $op         = 'between';
                        $sql_op     = 'between';
                        $sql_value  = $this->datetime_literal($matches[1],$matches[2],$matches[3],$matches[4],$matches[5],$matches[6]) . ' and ' .
                                      $this->datetime_literal($matches[7],$matches[8],$matches[9],$matches[10],$matches[11],$matches[12]);
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                        $resolved = 1;
                    }
                    elseif (preg_match('/^(-?[0-9]*\\.?[0-9]+)-(-?[0-9]*\\.?[0-9]+)$/', $value, $matches)) {
                        $op_implied = true;
                        $op         = 'between';
                        $sql_op     = 'between';
                        $sql_value  = $this->relative_datetime_literal($matches[1]) . ' and ' . $this->relative_datetime_literal($matches[2]);
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                        $resolved = 1;
                    }
                    if (!$resolved) {
                        $column     = $param;
                        $sql_value  = $value;
                        $op         = 'eq';
                        $op_implied = true;
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                        $resolved = 1;
                    }
                }
                else {   # INTEGER, FLOAT, STRING, TEXT
                    if (preg_match('/^(-?[0-9]*\\.?[0-9]+)-(-?[0-9]*\\.?[0-9]+)$/', $value, $matches) && $matches[1] <= $matches[2]) {
                        $column     = $param;
                        $op_implied = true;
                        $op         = 'between';
                        $sql_op     = 'between';
                        $sql_value  = "$matches[1] and $matches[2]";
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                        $resolved = 1;
                    }
                    elseif (preg_match('/^([a-zA-Z]+)-([a-zA-Z]+)$/', $value, $matches)) {
                        $column     = $param;
                        $op_implied = true;
                        $op         = 'between';
                        $sql_op     = 'between';
                        if ($this->case_sensitive) {
                            $sql_value  = "'" . strtolower($matches[1]) . "' and '" . strtolower($matches[2]) . "~'";   # "A-Z" results in "between 'a' and 'z~'"
                            $col_expr_begin = $this->func_strtolower . '(';
                            $col_expr_end   = ')';
                        }
                        else {
                            $sql_value  = "'" . $matches[1] . "' and '" . $matches[2] . "~'";                           # "A-Z" results in "between 'A' and 'Z~'"
                        }
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                        $resolved = 1;
                    }
                    elseif (preg_match('/[\\?\\*]+/', $value, $matches)) {
                        $column     = $param;
                        $op_implied = true;
                        $op         = 'matches';
                        $sql_op     = 'matches';
                        $sql_value  = $value;
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                        $resolved = 1;
                    }

                    if (!$resolved) {
                        $column     = $param;
                        $sql_value  = $value;
                        $op         = 'eq';
                        $op_implied = true;
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied bindable_value=$bindable_value\n");
                        $resolved = 1;
                    }
                }
            }

            if (!isset($sql_value) || ($op_implied && $sql_value === 'NULL')) {
                $sql_value = 'NULL';
                if     ($op === 'eq') { $sql_op = 'is';  }
                elseif ($op === 'in') { $sql_op = 'is'; }
                elseif ($op === 'ne') { $sql_op = 'is not'; }
                else                 { continue; }   // skip this param altogether
            }
            elseif ($op === 'contains') {
                $sql_op    = 'like';
                $sql_value = "*$sql_value*";
                $sql_value = preg_replace('/\\*/', '%', $sql_value);
                $sql_value = preg_replace('/\\?/', '_', $sql_value);
                if ($this->case_sensitive && preg_match('/[A-Za-z]/', $sql_value)) {
                    $sql_value = strtolower($sql_value);
                    $col_expr_begin = $this->func_strtolower . '(';
                    $col_expr_end   = ')';
                }
                $sql_value = $this->_quote($sql_value);
            }
            elseif ($op === 'matches') {
                $sql_op    = 'like';
                $sql_value = preg_replace('/\\*/', '%', $sql_value);
                $sql_value = preg_replace('/\\?/', '_', $sql_value);
                if ($this->case_sensitive && preg_match('/[A-Za-z]/', $sql_value)) {
                    $sql_value = strtolower($sql_value);
                    $col_expr_begin = $this->func_strtolower . '(';
                    $col_expr_end   = ')';
                }
                $sql_value = $this->_quote($sql_value);
            }
            elseif ($op === 'regexp') {
                # TODO
            }
            elseif ($op === 'between') {
                # nothing to do here. let it proceed.
            }
            else {
                if     ($op === 'eq') { $sql_op = '=';  if (!$op_implied) $bindable_value = $sql_value; }
                elseif ($op === 'in') { $sql_op = 'in'; }
                elseif ($op === 'ne') { $sql_op = '!='; $bindable_value = $sql_value; }
                elseif ($op === 'gt') { $sql_op = '>';  $bindable_value = $sql_value; }
                elseif ($op === 'ge') { $sql_op = '>='; $bindable_value = $sql_value; }
                elseif ($op === 'lt') { $sql_op = '<';  $bindable_value = $sql_value; }
                elseif ($op === 'le') { $sql_op = '<='; $bindable_value = $sql_value; }
                else                 { continue; }   // skip this param altogether

                if ($is_date_type) {
                    if (preg_match('/^([0-9][0-9][0-9][0-9])-?([0-9][0-9])-?([0-9][0-9])/', $sql_value, $matches)) {
                        if ($type === 'datetime' && $op === 'eq' && $op_implied) {
                            $sql_value  = $this->datetime_literal($matches[1],$matches[2],$matches[3]);
                            $op         = 'between';
                            $sql_op     = 'between';
                            $sql_value  = $this->datetime_literal($matches[1],$matches[2],$matches[3]) . ' and ' .
                                          $this->datetime_literal($matches[1],$matches[2],$matches[3],'23','59','59');
                        }
                        else {
                            $sql_value  = $this->date_literal($matches[1],$matches[2],$matches[3]);
                        }
                    }
                    elseif (preg_match('/^(-?[0-9]*\\.?[0-9]+)$/', $sql_value, $matches)) {
                        if ($op_implied) {
                            $op         = 'between';
                            $sql_op     = 'between';
                            if ($type === 'date') {
                                if ($matches[1] < 0) {
                                    $sql_value  = $this->relative_date_literal($matches[1]) . ' and ' . $this->relative_date_literal(0);
                                }
                                else {
                                    $sql_value  = $this->relative_date_literal(0) . ' and ' . $this->relative_date_literal($matches[1]);
                                }
                            }
                            else {
                                if ($matches[1] < 0) {
                                    $sql_value  = $this->relative_datetime_literal($matches[1]) . ' and ' . $this->relative_datetime_literal(0);
                                }
                                else {
                                    $sql_value  = $this->relative_datetime_literal(0) . ' and ' . $this->relative_datetime_literal($matches[1]);
                                }
                            }
                        }
                        else {
                            if ($type === 'date') {
                                $sql_value  = $this->relative_date_literal($matches[1]);
                            }
                            else {
                                $sql_value  = $this->relative_datetime_literal($matches[1]);
                            }
                        }
                    }
                    else {
                        continue;
                    }
                }
                else {
                    if (preg_match('/^-?\d\d*\.?\d*$/', $sql_value) ||
                        preg_match('/^-?\.\d+$/', $sql_value)) {
                        if ($op === 'in') {
                            $sql_op = '=';
                            $bindable_value = $sql_value;
                        }
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied bindable_value=$bindable_value\n");
                    }
                    elseif (preg_match('/^-?[\d\.]+,[-\d\.,]+\d$/', $sql_value)) {
                        if ($op === 'in' || ($op === 'eq' && $op_implied)) {
                            if (preg_match('/,/',$sql_value)) {
                                $sql_op    = 'in';
                                $sql_value = '(' . $sql_value . ')';
                            }
                            else {
                                $sql_op    = '=';
                                $bindable_value = $sql_value;
                            }
                        }
                        else {
                            $sql_value = "'" . $sql_value . "'";
                        }
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied bindable_value=$bindable_value\n");
                    }
                    else {
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied bindable_value=$bindable_value\n");
                        $sql_value = $this->_quote($sql_value);
                        #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied bindable_value=$bindable_value\n");
                        if ($op === 'in' || ($op === 'eq' && $op_implied)) {
                            if (preg_match('/,/',$sql_value)) {
                                $sql_op = 'in';
                                $sql_value = '(' . preg_replace('/,/', "','", $sql_value) . ')';
                            }
                        }
                    }
                }
            }

            if (!$column_def_found) {
                $column_expr = $column;
                #debug_print("expr[1]: column_expr=[$column_expr]\n");
            }
            elseif (preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $column)) {
                $relationship_alias = (isset($column_def['relationship_alias'])) ? $column_def['relationship_alias'] : null;
                if ($relationship_alias) {
                    if (!isset($rel_used[$relationship_alias])) {
                        $rel_used[$relationship_alias] = (isset($column_def['schema_name'])) ? $column_def['schema_name'] : 'Y';
                    }
                }
                else $relationship_alias = $table_def['table_alias'];

                if (isset($column_def['dbexpr'])) {
                    $column_expr = $column_def['dbexpr'];
                }
                else {
                    $column_expr = "$relationship_alias.$column";
                }
                #debug_print("expr[2]: column_expr=[$column_expr]\n");
            }
            elseif (preg_match('/^(.*[^ ]) +as +[A-Za-z_][A-Za-z0-9_]* *$/', $column, $matches)) {
                $column_expr = $matches[1];
                #debug_print("expr[3]: column_expr=[$column_expr]\n");
            }
            else {
                $column_expr = $column;   # this is some sort of expression, not a simple column
                #debug_print("expr[4]: column_expr=[$column_expr]\n");
            }

            if ($column_expr) {
                #debug_print("_make_where_clause($table,...): param(".__LINE__."): column=$column op=$op sql_value=$sql_value op_implied=$op_implied\n");
                if ($bind && isset($bindable_value)) {
                    $bind_values[] = $bindable_value;
                    $sql_value = '?';
                }
                $where_exprs[] = "$col_expr_begin$column_expr$col_expr_end $sql_op $sql_value";
            }
        }
        if (count($where_exprs) == 0) {
            $where_clause = '';
        }
        else {
            $where_clause = "\nwhere " . implode("\n  and ", $where_exprs);
        }
        if ($trace) trace_exit();
    }

    public function _quote ($str_value) {
        $str_value = preg_replace('/\'/', '\\\'', $str_value);
        return("'" . $str_value . "'");
    }

    protected function _make_order_by_clause ($table, $options, &$table_def = null, &$rel_used = null) {
        global $trace;
        if ($trace) trace_entry();
        $order_by_clause = '';
        if (isset($options) && isset($options['order_by'])) {
            $columns = $options['order_by'];
            $order_by_dbexpr = array();
            $matches = array();
            $i = 0;
            foreach ($columns as $column) {
                if (preg_match('/^([_a-z][_a-z0-9]*)\\.(asc|desc)$/i', $column, $matches)) {
                    $column = $matches[1];
                    $direction = " $matches[2]";
                }
                else {
                    $direction = '';
                }

                if (!isset($table_def)) {
                    $order_by_dbexpr[] = "$column$direction";
                }
                elseif (isset($table_def['column'][$column])) {
                    $order_by_dbexpr[] = "$column$direction";
                }
                elseif (preg_match('/^(.*[^ ]) +as +([A-Za-z_][A-Za-z0-9_]*) *$/', $column, $matches)) {
                    $order_by_dbexpr[] = $matches[2];
                }
                elseif (preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $column)) {
                    # do nothing. It is a column we don't understand. We selected NULL for it.
                }
                else {
                    $order_by_dbexpr[] = "$column$direction";   # this is some sort of expression, not a simple column
                }
            }
            if (count($order_by_dbexpr) > 0) {
                $order_by_clause = "\norder by\n   " . implode(",\n   ", $order_by_dbexpr);
            }
        }
        if ($trace) trace_exit($order_by_clause);
        return($order_by_clause);
    }

    public function begin_work () {
        global $trace;
        if ($trace) trace_entry();
        if ($trace) trace_exit();
    }

    public function commit () {
        global $trace;
        if ($trace) trace_entry();
        if ($trace) trace_exit();
    }

    public function rollback () {
        global $trace;
        if ($trace) trace_entry();
        if ($trace) trace_exit();
    }

    public function insert ($table, &$columns, $row = null, $options = null) {
        global $trace;
        if ($trace) trace_entry();

        $do_last_insert_id = (isset($options) && isset($options['last_insert_id']) && $options['last_insert_id']);
        $do_update         = (isset($options) && isset($options['update'])         && $options['update']);
        $do_init_not_null  = (isset($options) && isset($options['init_not_null'])  && $options['init_not_null']);

        if     (isset($row)     && $this->is_assoc($row))     { $data =& $row;     $return_ok = false; }
        elseif (isset($columns) && $this->is_assoc($columns)) { $data =& $columns; $return_ok = true; }
        else                                                  { $data = null;      $return_ok = false; }

        if ($do_last_insert_id || $do_update || $do_init_not_null) {
            $table_def = $this->get_table_def($table);
            $auto_increment_column = isset($table_def['auto_increment_column']) ? $table_def['auto_increment_column'] : null;
            $primary_key           = isset($table_def['primary_key'])           ? $table_def['primary_key']           : array();
            $num_primary_key_cols  = count($primary_key);
            if (isset($data)) $current_id = isset($data[$auto_increment_column]) ? $data[$auto_increment_column] : 0;
            else              $current_id = 0;     # ?!? shouldn't I search through the numeric array? for now, just disallow.
            if ($do_last_insert_id && ($current_id != 0 || !$return_ok)) {
                $do_last_insert_id = false;
                unset($options['last_insert_id']);
            }
        }

        if ($do_init_not_null && isset($data)) {
            $current_dttm = date('Y-m-d H:i:s');
            $current_dt   = date('Y-m-d');
            if (isset($table_def['phys_columns'])) {
                foreach ($table_def['phys_columns'] as $column) {
                    if (isset($auto_increment_column) && $column === $auto_increment_column) continue;
                    $not_null_ind = $table_def['column'][$column]['not_null_ind'];
                    $default_value = $table_def['column'][$column]['default_value'];
                    $value = isset($data[$column]) ? $data[$column] : null;
                    if ($not_null_ind === 'Y' && (!isset($value) || $value === '')) {
                        if (isset($default_value)) {
                            if ($default_value === 'CURRENT_TIMESTAMP') $data[$column] = $current_dttm;
                            else                                        $data[$column] = $default_value;
                        }
                        else {
                            $column_type = $table_def['column'][$column]['column_type'];
                            if ($column_type === 'integer' || $column_type === 'float') {
                                $data[$column] = 0;
                            }
                            elseif ($column_type === 'string' || $column_type === 'blob' || $column_type === 'text') {
                                $max_length = $table_def['column'][$column]['max_length'];
                                $data[$column] = ($max_length < 3) ? substr('tbd',0,$max_length) : 'tbd';
                            }
                            elseif ($column_type === 'datetime') {
                                $data[$column] = $current_dttm;
                            }
                            elseif ($column_type === 'date') {
                                $data[$column] = $current_dt;
                            }
                        }
                    }
                }
            }
            else {
                foreach ($data as $column => $value) {
                    if (isset($auto_increment_column) && $column === $auto_increment_column) continue;
                    if ($table_def['column'][$column]['not_null_ind'] === 'Y' && (!isset($value) || $value === '')) {
                        $default_value = $table_def['column'][$column]['default_value'];
                        if (isset($default_value)) {
                            if ($default_value === 'CURRENT_TIMESTAMP') $data[$column] = $current_dttm;
                            else                                        $data[$column] = $default_value;
                        }
                        else {
                            $column_type = $table_def['column'][$column]['column_type'];
                            if ($column_type === 'integer' || $column_type === 'float') {
                                $data[$column] = 0;
                            }
                            elseif ($column_type === 'string' || $column_type === 'blob' || $column_type === 'text') {
                                $max_length = $table_def['column'][$column]['max_length'];
                                $data[$column] = ($max_length < 3) ? substr('tbd',0,$max_length) : 'tbd';
                            }
                            elseif ($column_type === 'datetime') {
                                $data[$column] = $current_dttm;
                            }
                            elseif ($column_type === 'date') {
                                $data[$column] = $current_dt;
                            }
                        }
                    }
                }
            }
        }

        $bind_values = array();
        $sql = $this->make_insert_row_sql($table, $columns, $row, $options, $bind_values);
        if ($do_update && $current_id != 0) {
            try {
                $nrows = $this->execute($sql, $bind_values);
            }
            catch (\Exception $e) {
                $message = $e->getMessage();
                if (!preg_match('/duplicate/i', $message)) throw new \Exception($message, $e->getCode(), $e);
                $nrows = $this->update($table, $columns, $row);
            }
        }
        else {
            $nrows = $this->execute($sql, $bind_values);
        }

        if ($do_last_insert_id && $current_id == 0) {
            $data[$auto_increment_column] = $this->last_insert_id($table);
        }

        if ($trace) trace_exit($nrows);
        return $nrows;
    }

    public function insert_rows ($table, &$columns, $rows = null, $options = null) {
        global $trace;
        if ($trace) trace_entry();
        $nrows = 0;

        if (isset($rows)) {
            $count = count($columns);
            for ($i = 0; $i < $count; $i++) {
                $row =& $rows[$i];
                $nrows += $this->insert($table, $columns, $row, $options);
            }
        }
        else {
            $count = count($columns);   # these are really rows. the caller used the 2nd position ($columns) to pass them.
            for ($i = 0; $i < $count; $i++) {
                $row =& $columns[$i];
                $nrows += $this->insert($table, $row, null, $options);
            }
        }

        if ($trace) trace_exit($nrows);
        return $nrows;
    }

    public function update ($table, $params, $columns = null, $row = null, $options = null) {
        global $trace;
        if ($trace) trace_entry();
        $bind_values = array();
        $sql = $this->make_update_sql($table, $params, $columns, $row, $options, $bind_values);
        $nrows = $this->execute($sql, $bind_values);
        if ($trace) trace_exit($nrows);
        return $nrows;
    }

    public function delete ($table, $params, $options = null) {
        global $trace;
        if ($trace) trace_entry();
        $bind_values = array();
        $sql = $this->make_delete_sql($table, $params, $options, $bind_values);
        $nrows = $this->execute($sql, $bind_values);
        if ($trace) trace_exit($nrows);
        return $nrows;
    }

    public function execute ($sql, $bind_values = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        throw new \Exception("ERROR: execute() method needs to be implemented in a subclass");
        if ($trace) trace_exit($nrows);
        return $nrows;
    }

    protected function _make_limit_clause ($table, $options) {
        global $trace;
        if ($trace) trace_entry();
        $limit_clause = '';
        if ($trace) trace_exit($limit_clause);
        return($limit_clause);
    }

    public function remove_limit_clause (&$sql) {
        $sql = preg_replace('/[ \\t\\n]+offset +[0-9]+[ \\t\\n]*$/', '', $sql);
        $sql = preg_replace('/[ \\t\\n]+limit +[0-9][0-9, \\t\\n]*$/', '', $sql);
    }

    public function add_limit_clause (&$sql, &$options = null) {
        if (isset($options)) {
            $sql .= $this->_make_limit_clause('default', $options);
        }
    }

    public function make_insert_row_sql ($table, $columns, $row=null, $options = null, &$bind_values = null) {
        global $trace;
        if ($trace) trace_entry();

        if (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $table, $matches)) {
            $schema    = $matches[1];
            $fulltable = $table;
            $table     = $matches[2];
        }
        else {
            if (!isset($schema)) $schema = $this->schema_name;
            $fulltable = "$schema.$table";
        }

        $raw = (isset($options) && isset($options['raw']) && $options['raw']) ? 1 : 0;

        if (!$raw) {
            $table_def = $this->get_table_def($table);
            $column_defs =& $table_def['column'];
        }

        # $nrows = $db->insert('user', 'username,email', 'scott,scott@tiger.com');
        if (is_scalar($columns)) $columns = explode(',',$columns);
        if (is_scalar($row))     $row     = explode(',',$row);

        # $nrows = $db->insert('user', $user_assoc_array);
        if (isset($columns) && !isset($row)) {
            if (!$this->is_assoc($columns)) throw new \Exception("Database($this->name).make_insert_row_sql($table,...): row was not supplied and columns is not an associative array");
            $row = $columns;
            $columns = null;
        }
        # $nrows = $db->insert('user', null, $user_assoc_array);
        if (!isset($columns) && $this->is_assoc($row)) {
            $columns = array_keys($row);
        }
        # $nrows = $db->insert('user', $columns, $user_assoc_array);
        if ($this->is_assoc($row)) {
            $new_row = array();
            foreach ($columns as $column) {
                $new_row[] = $row[$column];
            }
            $row = $new_row;
        }

        $sql = "insert into $fulltable\n";
        $values = "values\n";
        $value_count = isset($row) ? count($row) : 0;

        foreach ($columns as $colnum => $column) {
            if ($raw || (isset($column_defs[$column]) && isset($column_defs[$column]['physical_ind']) && $column_defs[$column]['physical_ind'] === 'Y' && $column !== 'owner_group_id')) {
                if ($value_count == 0) {
                    $value = '?';
                }
                else {
                    $value = $row[$colnum];
                    if ($value == null) {
                        $value = 'NULL';
                    }
                    else {
                        $quoted = (!$raw && isset($column_defs[$column]['quoted'])) ? ($column_defs[$column]['quoted']) : (!preg_match('/^-?([0-9]+\\.?[0-9]*|\\.[0-9]+)$/',$value));
                        if ($quoted) {
                            $value = $this->_quote($value);
                        }
                    }
                }
                $sql .= ($colnum == 0) ? "  ($column" : ",\n   $column";
                if (!$raw && isset($column_defs[$column]['dbexpr_update'])) {
                    throw new \Exception("Database($this->name).make_insert_row_sql($table,...): dbexpr_update not yet implemented");
                    # PERL: $value = sprintf($column_defs->{$column}{dbexpr_update}, $value);
                }
                $values .= ($colnum == 0) ? "  ($value" : ",\n   $value";
            }
        }

        # This is MAGIC. Any record created in a table with an "owner_group_id" automatically gets it filled in with the current Group ID
        $group_id = $this->context->getAuthenticatedGroupId();
        if ($group_id != APP_SYSTEM_GROUP_ID && isset($column_defs) && isset($column_defs['owner_group_id'])) {
            $sql    .= ",\n   owner_group_id";
            $values .= ",\n   $group_id";
        }

        $sql .= ")\n";
        $values .= ")\n";
        $sql .= $values;

        if ($trace) trace_exit($sql);
        return $sql;
    }

    public function make_update_sql ($table, $params, $columns = null, $row = null, $options = null, &$bind_values = null) {
        global $trace;
        if ($trace) trace_entry();

        if (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $table, $matches)) {
            $schema    = $matches[1];
            $fulltable = $table;
            $table     = $matches[2];
        }
        else {
            if (!isset($schema)) $schema = $this->schema_name;
            $fulltable = "$schema.$table";
        }

        $raw = (isset($options) && isset($options['raw'])) ?  $options['raw'] : 0;
        $by_expression = (isset($options) && isset($options['by_expression'])) ?  $options['by_expression'] : 0;

        $bind = isset($bind_values);
        $where_bind_values = $bind ? array() : null;

        if (!$raw) {
            $table_def = $this->get_table_def($table);
            $column_defs =& $table_def['column'];
        }

        # $nrows = $db->update('user', $user_assoc_array);
        if (!isset($columns) && !isset($row)) {
            if (!isset($params)) throw new \Exception("Database($this->name).make_update_sql($table,...): params, columns, and row were not supplied");
            if (!is_array($params)) throw new \Exception("Database($this->name).make_update_sql($table,...): columns and row were not supplied and params is not an array ($params)");
            if (!$this->is_assoc($params)) throw new \Exception("Database($this->name).make_update_sql($table,...): columns and row were not supplied and params is not an associative array");
            $row = $params;
            $params = null;
        }
        else {
            # $nrows = $db->update('user', $params, 'username', 'scott');
            if (is_scalar($columns)) $columns = array($columns);
            if (is_scalar($row))     $row     = array($row);
        }
        # $nrows = $db->update('user', $params, $user_assoc_array);
        if (isset($columns) && !isset($row)) {
            if (!$this->is_assoc($columns)) throw new \Exception("Database($this->name).make_update_sql($table,...): row was not supplied and columns is not an associative array");
            $row = $columns;
            $columns = null;
        }
        # $nrows = $db->update('user', $params, null, $user_assoc_array);
        if (!isset($columns) && $this->is_assoc($row)) {
            $columns = array_keys($row);
        }
        # $nrows = $db->update('user', $params, $columns, $user_assoc_array);
        if ($this->is_assoc($row)) {
            $new_row = array();
            foreach ($columns as $column) {
                $new_row[] = $row[$column];
            }
            $row = $new_row;
        }

        $noupdate_hash    = array();
        $noupdate         = array();
        foreach ($columns as $i => $column) {
            $noupdate[$i] = 0;
        }
        $where = '';
        $value_count = isset($row) ? count($row) : 0;

        if (! isset($params) || is_scalar($params)) {
            if ($raw) throw new \Exception("Database($this->name).make_update_sql($table,...): params-implied-by-primary-key (null or scalar params) not allowed in raw mode");
            $primary_key_column = $this->get_primary_key_column($table);
            if (is_scalar($params)) $key = $params;
            else {
                $i = array_search($primary_key_column, $columns);
                if ($i === false) throw new \Exception("Database($this->name).make_update_sql($table,...): primary key column ($primary_key_column) not found in column list");
                $key = $row[$i];
            }
            $params = array("$primary_key_column-eq" => $key);
            $noupdate_hash[$primary_key_column] = 1;
        }

        if (!is_array($params)) {
            throw new \Exception("Database($this->name).make_update_sql($table,...): unrecognized params type");
        }
        elseif ($this->is_assoc($params)) {
            $where_clause = '';
            $this->_make_where_clause($table, $params, $where_clause, $where_bind_values);
        }
        else {   # numeric array
            if (count($params) == 0) throw new \Exception("Database($this->name).make_update_sql($table,...): no key column indexes supplied in params");
            $where_array = array();
            if (preg_match('/^[0-9]+$/',$params[0])) {  # an array of indexes
                $keycolidx = $params;  # @$params represents a set of array indices
                foreach ($keycolidx as $i => $colidx) {
                    $column = $columns[$colidx];
                    if ($value_count == 0) {
                        $value = '?';
                        if (!$raw && isset($column_defs[$column]['dbexpr_update'])) {
                            throw new \Exception("Database($this->name).make_update_sql($table,...): dbexpr_update not yet implemented");
                            # PERL: $value = sprintf($column_defs->{$column}{dbexpr_update}, $value, $value, $value, $value, $value);
                        }
                    }
                    else {
                        $value = $row[$colidx];
                        if ($value == null) {
                            $value = 'NULL';
                        }
                        elseif (!$raw && isset($column_defs[$column]['dbexpr_update'])) {
                            throw new \Exception("Database($this->name).make_update_sql($table,...): dbexpr_update not yet implemented");
                            # PERL: $value = sprintf($column_defs->{$column}{dbexpr_update}, $value, $value, $value, $value, $value);
                        }
                        else {
                            $quoted = (!$raw && isset($column_defs[$column]['quoted'])) ? ($column_defs[$column]['quoted']) : (!preg_match('/^-?([0-9]+\\.?[0-9]*|\\.[0-9]+)$/',$value));
                            if ($quoted) {
                                $value = $this->_quote($value);
                            }
                        }
                    }
                    $where_array[]        = "$column = $value";
                    $where_bind_var_idx[] = $colidx;
                    $noupdate[$colidx]    = 1;
                }
            }
            else {   # an array of column names
                throw new \Exception("Database($this->name).make_update_sql($table,...): params as array of column names not implemented");
            }
            if (count($where_array) > 0)
                $where_clause = "where " . implode("\n  and ",$where_array) . "\n";
        }

        # Now determine what to "set"
        if (!is_array($row)) {   # this section is old code. now, the $row must always be an array
            foreach ($columns as $colidx => $column) {
                if ($noupdate[$colidx]) continue;
                if (isset($noupdate_hash[$column])) continue;
                $value = '?';
                if (!$raw && isset($column_defs[$column]['dbexpr_update'])) {
                    throw new \Exception("Database($this->name).make_update_sql($table,...): dbexpr_update not yet implemented");
                    # PERL: $value = sprintf($column_defs->{$column}{dbexpr_update}, $value, $value, $value, $value, $value);
                }
                $set[] = "$column = $value";
                if ($bind) $bind_values[] = $row[$colidx];
            }
        }
        else {
            foreach ($columns as $colidx => $column) {
                if ($noupdate[$colidx]) continue;
                if (isset($noupdate_hash[$column])) continue;
                if (!isset($row[$colidx])) {
                    $set[] = "$column = null";
                }
                else {
                    if ($bind) {
                        if (!$raw && isset($column_defs[$column]['dbexpr_update'])) {
                            throw new \Exception("Database($this->name).make_update_sql($table,...): dbexpr_update not yet implemented");
                            # PERL: $value = sprintf($column_defs->{$column}{dbexpr_update}, $value, $value, $value, $value, $value);
                        }
                        $set[]         = "$column = ?";
                        $bind_values[] = $row[$colidx];
                    }
                    else {
                        $value         = $row[$colidx];
                        $quoted        = (!$raw && isset($column_defs[$column]['quoted'])) ? ($column_defs[$column]['quoted']) : (!preg_match('/^-?([0-9]+\\.?[0-9]*|\\.[0-9]+)$/',$value));
                        if ($quoted && !$by_expression) $value = $this->_quote($value);
                        $set[]         = "$column = $value";
                    }
                }
            }
        }

        $sql = "update $fulltable set\n   " . implode(",\n   ",$set) . $where_clause;

        # put the bind values for the where clause onto the end
        if ($bind && count($where_bind_values) > 0) {
            foreach ($where_bind_values as $bind_value) {
                $bind_values[] = $bind_value;
            }
        }

        if ($trace) trace_exit($sql);
        return $sql;
    }

    public function make_delete_sql ($table, $params, $options = null, &$bind_values = null) {
        global $trace;
        if ($trace) trace_entry();

        if (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $table, $matches)) {
            $schema    = $matches[1];
            $fulltable = $table;
            $table     = $matches[2];
        }
        else {
            if (!isset($schema)) $schema = $this->schema_name;
            $fulltable = "$schema.$table";
        }

        $raw = (isset($options) && isset($options['raw'])) ?  $options['raw'] : 0;
        $by_expression = (isset($options) && isset($options['by_expression'])) ?  $options['by_expression'] : 0;

        $bind = isset($bind_values);
        $where_bind_values = $bind ? array() : null;

        $table_def = null;
        if (!$raw) {
            $table_def = $this->get_table_def($table);
            $column_defs =& $table_def['column'];
        }

        $where = '';

        if (! isset($params)) throw new \Exception("Database($this->name).make_delete_sql($table,...): cannot delete with null params supplied");
        if (is_scalar($params)) {
            if ($raw) throw new \Exception("Database($this->name).make_delete_sql($table,...): params-implied-by-primary-key (scalar params) not allowed in raw mode");
            $primary_key_column = $this->get_primary_key_column($table, $table_def);
            $key = $params;
            $params = array("$primary_key_column-eq" => $key);
        }

        $where_clause = '';
        $this->_make_where_clause($table, $params, $where_clause, $bind_values);

        $sql = "delete from $fulltable " . $where_clause;

        if ($trace) trace_exit($sql);
        return $sql;
    }

    /**
     * make_create_table_ddl($table_def, $ddls) - tbd
     * @param string  table_name         [IN] - the name of the table
     * @param string  table_label        [IN] - (optional) the label (text string) used to describe this table in the user interface (default is the table_name, converted to a label)
     * @param string  columns            [IN] - either a (csv) list of column_names or an array of column_names
     * @param string  phys_columns       [IN] - (optional) either a (csv) list of column_names or an array of column_names
     *                                          (if phys_columns exists, it overrides the columns value, which is instead interpreted to be a list of logical columns)
     * @param object  column             [IN] - (optional) an associated array of associative arrays with metadata about the columns
     *                                          e.g. array('person_id'=>array('column_name'=>'person_id','column_label'=>'Person ID','column_type'=>'integer','max_length'=>'10','not_null_ind'=>'Y',
                                                           'primary_key_ind'=>'Y','auto_increment_ind'=>'Y','default_value'=>null,'display_format'=>null,'display_justify'=>null))
     * @param array   primary_key        [IN] - (optional) an array of column_names which make up the primary key (if single column 'id' or ending in '_id', then it is an autoincrement column)
     * @param string  auto_increment_column [IN] - (optional) a column_name which is an autoincrement column
     * @param array   unique_indexes     [IN] - (optional) an array of arrays of column_names which make up alternate keys (uniqueness constraints with indexes)
     * @param array   indexes            [IN] - (optional) an array of arrays of column_names which make up non-unique indexes
     */
    public function make_create_table_ddl (&$table_def, &$ddls) {
        global $trace;
        if ($trace) trace_entry();

        $this->_make_canonical_table_def($table_def);
        $phys_columns = $table_def['phys_columns'];
        $column_defs  = $table_def['column'];

        $ddl = "create table $this->schema_name.$table_def[table_name] (";
        foreach ($phys_columns as $i => $column_name) {
            if ($i > 0) $ddl .= ',';
            $native_type = $this->app_type_to_native_type($column_defs[$column_name]['column_type'],
                                                          $column_defs[$column_name]['max_length'],
                                                          $column_defs[$column_name]['number_of_decimals'],
                                                          $column_name);
            $null_clause = $column_defs[$column_name]['not_null_ind'] === 'Y' ? 'not null' : 'null';
            $ddl .= sprintf("\n    %-20s %-12s %s", $column_name, $native_type, $null_clause);
        }
        $ddl .= "\n)";
        $ddls[] = $ddl;

        if ($trace) trace_exit();
    }

    /**
     * make_alter_table_ddl($table_def, $table_def_from_db, $ddls) - tbd
     * @param string  table_name         [IN] - the name of the table
     * @param string  table_label        [IN] - (optional) the label (text string) used to describe this table in the user interface (default is the table_name, converted to a label)
     * @param string  columns            [IN] - either a (csv) list of column_names or an array of column_names
     * @param string  phys_columns       [IN] - (optional) either a (csv) list of column_names or an array of column_names
     *                                          (if phys_columns exists, it overrides the columns value, which is instead interpreted to be a list of logical columns)
     * @param object  column             [IN] - (optional) an associated array of associative arrays with metadata about the columns
     *                                          e.g. array('person_id'=>array('column_name'=>'person_id','column_label'=>'Person ID','column_type'=>'integer','max_length'=>'10','not_null_ind'=>'Y',
                                                           'primary_key_ind'=>'Y','auto_increment_ind'=>'Y','default_value'=>null,'display_format'=>null,'display_justify'=>null))
     * @param array   primary_key        [IN] - (optional) an array of column_names which make up the primary key (if single column 'id' or ending in '_id', then it is an autoincrement column)
     * @param string  auto_increment_column [IN] - (optional) a column_name which is an autoincrement column
     * @param array   unique_indexes     [IN] - (optional) an array of arrays of column_names which make up alternate keys (uniqueness constraints with indexes)
     * @param array   indexes            [IN] - (optional) an array of arrays of column_names which make up non-unique indexes
     */
    public function make_alter_table_ddl (&$table_def, $table_def_from_db, &$ddls) {
        global $trace;
        if ($trace) trace_entry();

        $this->_make_canonical_table_def($table_def);

        if ($trace) trace_exit();
    }

    /**
     * make_drop_table_ddl($table_name, $ddls) - tbd
     * @param string  table_name         [IN] - the name of the table
     */
    public function make_drop_table_ddl ($table_name, &$ddls) {
        global $trace;
        if ($trace) trace_entry();
        $ddl = "drop table if exists $this->schema_name.$table_name";
        $ddls[] = $ddl;
        if ($trace) trace_exit();
    }

    public function make_rename_table_ddl ($table_name, $new_table_name, &$ddls) {
        global $trace;
        if ($trace) trace_entry();
        $ddl = "rename table $this->schema_name.$table_name to $this->schema_name.$new_table_name";
        $ddls[] = $ddl;
        if ($trace) trace_exit();
    }

    public function make_export_table_sql ($table_name, $params, $columns, $options, $filename, &$sqls) {
        global $trace;
        if ($trace) trace_entry();
        throw new \Exception("make_export_table_sql() must be implemented in a subclass");
        if ($trace) trace_exit();
    }

    public function make_import_table_sql ($table_name, $columns, $options, $filename, &$sqls) {
        global $trace;
        if ($trace) trace_entry();
        throw new \Exception("make_import_table_sql() must be implemented in a subclass");
        if ($trace) trace_exit();
    }

    public function _make_canonical_table_def (&$table_def) {
        global $trace;
        if ($trace) trace_entry();

        $table_name = isset($table_def['table_name']) ?  $table_def['table_name'] : null;
        if (!isset($table_name)) throw new \Exception("Cannot make create table ddl without a table name");

        if (!isset($table_def['table_label'])) {
            $table_def['table_label'] = $this->make_label($table_name);
        }

        if (!isset($table_def['column'])) {
            $table_def['column'] = array();
        }
        $column_defs  =& $table_def['column'];

        $phys_columns = isset($table_def['phys_columns']) ?  $table_def['phys_columns'] : null;
        if (isset($phys_columns)) {
            if (is_scalar($phys_columns)) {
                $phys_columns = explode(',',$phys_columns);
                $table_def['phys_columns'] = $phys_columns;
            }
        }

        $columns      = isset($table_def['columns'])      ?  $table_def['columns']      : null;
        if (isset($columns)) {
            if (is_scalar($columns)) {
                $columns = explode(',',$columns);
                $table_def['columns'] = $columns;
            }
        }
        else {
            $columns = $phys_columns;
        }

        if (!isset($columns)) {
            throw new \Exception("Either 'columns' or 'phys_columns' must be supplied in order to have a complete canonical table definition");
        }

        $column_specs = $columns;
        $columns = array();
        $matches = array();
        $assumed_primary_key = array();
        foreach ($column_specs as $i => $column_spec) {
            #debug_print("$table_name.$column_spec");
            $primary_key_ind    = null;
            $auto_increment_ind = null;
            $max_length         = null;
            $not_null_ind       = 'N';
            if (preg_match('/^([a-z0-9_]+):([a-z]+):?([0-9]*)$/', $column_spec, $matches)) {
                $column_name = $matches[1];
                $column_type = $matches[2];
                $max_length  = $matches[3] ? $matches[3] : null;
            }
            else {
                $column_name = $column_spec;
                $column_type = null;
                $max_length  = null;
            }
            $columns[] = $column_name;
            $new_column_def = 0;
            if (!isset($column_defs[$column_name])) {
                $column_defs[$column_name] = array();
                $new_column_def = 1;
            }
            if (!isset($column_defs[$column_name]['column_name']))  $column_defs[$column_name]['column_name']  = $column_name;
            if (!isset($column_defs[$column_name]['column_type'])) {
                if (!isset($column_type)) {
                    if ($column_name === 'id' || preg_match('/_id$/', $column_name)) {
                        $column_type = 'integer';
                        if ($i == 0) {
                            $not_null_ind = 'Y';
                            $primary_key_ind = 'Y';
                            $auto_increment_ind = 'Y';
                        }
                    }
                    elseif (preg_match('/_(dt|date)$/', $column_name)) {
                        $column_type = 'date';
                    }
                    elseif (preg_match('/_dttm$/', $column_name)) {
                        $column_type = 'datetime';
                    }
                    else {
                        $column_type = 'string';
                        $max_length  = 80;
                    }
                }
                $column_defs[$column_name]['column_type']  = $column_type;
                if (isset($max_length) && !isset($column_defs[$column_name]['max_length'])) $column_defs[$column_name]['max_length'] = $max_length;
            }
            if (!isset($column_defs[$column_name]['not_null_ind']))       { $column_defs[$column_name]['not_null_ind']  = $not_null_ind; }
            if (!isset($column_defs[$column_name]['number_of_decimals'])) { $column_defs[$column_name]['number_of_decimals'] = null; }
        }
        $table_def['columns'] = $columns;

        #debug_print(json_encode($table_def,JSON_PRETTY_PRINT));
        if ($trace) trace_exit($table_def);
    }

    ######################################################################################
    # Note: the following functions are correct for MySQL. They should be overridden.
    ######################################################################################
    public function date_to_year ($date) {
        return("date_format($date,'%Y-01-01')");
    }

    public function date_to_month ($date) {
        return("date_format($date,'%Y-%m-01')");
    }

    public function date_interval ($date, $interval_days, $offset_days) {
        return("date_format($date,'%Y-%m-01')");
                        if     ($modifier === 'year')  { $dbexpr = $this->date_to_year($dbexpr);  }
                        elseif ($modifier === 'month') { $dbexpr = $this->date_to_month($dbexpr); }
    }

    public function date_modified ($date, $modifier) {
        return("date_format($date,'%Y-%m-01')");
    }

    public function datetime_to_year ($datetime) {
        return("date_format($datetime,'%Y-01-01 00:00:00')");
    }

    public function datetime_to_month ($datetime) {
        return("date_format($datetime,'%Y-%m-01 00:00:00')");
    }

    public function datetime_to_day ($datetime) {
        return("date_format($datetime,'%Y-%m-%d 00:00:00')");
    }

    public function datetime_to_hour ($datetime) {
        return("date_format($datetime,'%Y-%m-%d %H:00:00')");
    }

    public function datetime_to_minute ($datetime) {
        return("date_format($datetime,'%Y-%m-%d %H:%i:00')");
    }

    public function datetime_interval ($datetime, $interval_sec, $offset_sec=0) {
        $dbexpr = "from_unixtime(unix_timestamp(sample_dttm)-mod(unix_timestamp(sample_dttm),$interval_sec),'%Y-%m-%d %H:%i:%S')";
        return($dbexpr);
    }

    public function datetime_modified ($datetime, $modifier) {
        global $trace;
        if ($trace) trace_entry();
        if     ($modifier === 'year')   { $dbexpr = $this->datetime_to_year($datetime);   }
        elseif ($modifier === 'month')  { $dbexpr = $this->datetime_to_month($datetime);  }
        elseif ($modifier === 'day')    { $dbexpr = $this->datetime_to_day($datetime);    }
        elseif ($modifier === 'hour')   { $dbexpr = $this->datetime_to_hour($datetime);   }
        elseif ($modifier === 'minute') { $dbexpr = $this->datetime_to_minute($datetime); }
        else {
            if (preg_match('/^([0-9]+)(year|month|day|hour|minute)$/', $modifier, $matches)) {
                $interval_num  = $matches[1];
                $interval_unit = $matches[2];
                $interval_sec  = 0;
                if     ($interval_unit === 'year')   { $interval_sec = $interval_num * 365*24*3600; }
                elseif ($interval_unit === 'month')  { $interval_sec = $interval_num * 30*24*3600; }
                elseif ($interval_unit === 'day')    { $interval_sec = $interval_num * 24*3600; }
                elseif ($interval_unit === 'hour')   { $interval_sec = $interval_num * 3600; }
                elseif ($interval_unit === 'minute') { $interval_sec = $interval_num * 60; }
                else                                 { $dbexpr = $datetime; }
                if ($interval_sec) {
                    $dbexpr = $this->datetime_interval($datetime, $interval_sec);
                }
            }
            else {
                $dbexpr = $datetime;
            }
        }
        if ($trace) trace_exit($dbexpr);
        return($dbexpr);
    }

    private function column_modified ($column, $column_modifier, $raw_dbexpr, $column_def, $table_def, $aggregate) {
        global $trace;
        if ($trace) trace_entry();
        if (preg_match('/^(sum|min|max|avg|stddev|count)$/', $column_modifier)) {
            $dbexpr = "$column_modifier($raw_dbexpr)";
        }
        elseif ($column_modifier === 'countd') {
            $dbexpr = "count(distinct $raw_dbexpr)";
        }
        elseif ($column_def['column_type'] === 'datetime') {
            $dbexpr = $this->datetime_modified($raw_dbexpr, $column_modifier);
        }
        elseif ($column_def['column_type'] === 'date') {
            $dbexpr = $this->date_modified($raw_dbexpr, $column_modifier);
        }
        else {
            $dbexpr = $raw_dbexpr;
        }
        if ($trace) trace_exit($dbexpr);
        return($dbexpr);
    }

    public function date_stdformat ($dbexpr) {
        return($dbexpr);
    }

    public function datetime_stdformat ($dbexpr) {
        return($dbexpr);
    }

    public function datetime_literal ($year, $month, $day, $hours = '00', $min = '00', $sec = '00') {
        if ($sec === '') $sec = '00';
        return("'$year-$month-$day $hours:$min:$sec'");
    }

    public function relative_date_literal ($days, $base_date = null) {
        if ($base_date == null) return("date_add(curdate(),INTERVAL $days DAY)");
        else                    return("date_add('$base_date',INTERVAL $days DAY)");
    }

    public function relative_datetime_literal ($days, $hours = '00', $min = '00', $sec = '00', $base_datetime = null) {
        if ($days == intval($days)) {
            if ($base_date == null) return("date_add(now(),INTERVAL '$days $hours:$min:$sec' DAY_SECOND)");
            else                    return("date_add('$base_datetime',INTERVAL '$days $hours:$min:$sec' DAY_SECOND)");
        }
        else {
            if ($base_date == null) return("date_add(now(),INTERVAL $days DAY)");
            else                    return("date_add('$base_date',INTERVAL $days DAY)");
        }
    }
}

#$pdo = new PDO(
#    'mysql:host=hostname;dbname=ssldb',
#    'username',
#    'password',
#    array(
#        PDO::MYSQL_ATTR_SSL_KEY    =>'/path/to/client-key.pem',
#        PDO::MYSQL_ATTR_SSL_CERT=>'/path/to/client-cert.pem',
#        PDO::MYSQL_ATTR_SSL_CA    =>'/path/to/ca-cert.pem'
#    )
#);

